/**
 * 
 */
package com.java.test.user.mapper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.java.test.datacenter.dto.SumAccountInfo;
import com.java.test.user.dto.ReflectInfo;
import com.java.test.util.PageInfo;

/**
 * @author Administrator
 *
 */
@Component
public class ReflectMapper {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public int selectreflectCount(ReflectInfo info) {
		String sql = "select count(*) from member_reflect where 1=1 ";

		if (info.getStartTime() != null && !"".equals(info.getStartTime())) {
			sql += " and reflect_time>='" + info.getStartTime() + "'";
		}

		if (info.getEndTime() != null && !"".equals(info.getEndTime())) {
			sql += " and reflect_time<='" + info.getEndTime() + "'";
		}

		if (info.getCurDate() != null && !"".equals(info.getCurDate())) {
			sql += " and TO_DAYS(reflect_time)=TO_DAYS('" + info.getCurDate() + "')";
		}

		if (info.getReflectChannel() != 0) {
			sql += " and reflect_channel='" + info.getReflectChannel() + "'";
		}

		if (info.getStatus() != 0) {

			sql += "and status='" + info.getStatus() + "'";
		}
		if (info.getGameAccount() != null && !"".equals(info.getGameAccount())) {
			sql += " and game_account like '%" + info.getGameAccount() + "%'";
		}

		Integer i = jdbcTemplate.queryForObject(sql, Integer.class);

		return i == null ? 0 : i;

	}

	public List<ReflectInfo> selectreflectList(PageInfo<ReflectInfo> page, ReflectInfo info) {
		String sql = "select * from member_reflect where 1=1 ";
		if (info.getStartTime() != null && !"".equals(info.getStartTime())) {
			sql += " and reflect_time>='" + info.getStartTime() + "'";
		}

		if (info.getEndTime() != null && !"".equals(info.getEndTime())) {
			sql += " and reflect_time<='" + info.getEndTime() + "'";
		}

		if (info.getCurDate() != null && !"".equals(info.getCurDate())) {
			sql += " and TO_DAYS(reflect_time)=TO_DAYS('" + info.getCurDate() + "')";
		}

		if (info.getReflectChannel() != 0) {
			sql += " and reflect_channel='" + info.getReflectChannel() + "'";
		}

		if (info.getStatus() != 0) {

			sql += "and status='" + info.getStatus() + "'";
		}
		if (info.getGameAccount() != null && !"".equals(info.getGameAccount())) {
			sql += " and game_account like '%" + info.getGameAccount() + "%'";
		}

		int start = (page.getCurrentPage() - 1) * page.getPageCount();
		int limit = page.getPageCount();

		sql += " order by review_time desc limit ?,?";
		List<ReflectInfo> list = jdbcTemplate.query(sql, new ReflectInfo(), start, limit);
		return list;
	}

	public ReflectInfo selectreflectList(String gameAccount) {
		String sql = "select * from member_reflect where  game_account = ? and status=1 order by reflect_time desc limit 0,1";

		List<ReflectInfo> list = jdbcTemplate.query(sql, new ReflectInfo());

		if (list == null || list.size() <= 0)
			return null;

		return list.get(0);
	}

	public void plinsertdepositInfo(List<ReflectInfo> list) {
		String sql = "INSERT INTO `member_reflect` "
				+ "( `game_account`, `reflect_amount`, `reflect_time`, `reflect_channel`,"
				+ " `bank_account`, `status`,reviewer,review_time,sxf_charge,dzje_amount,reflect_channel_name)VALUES ( ?, ?, ?, ?, ?, ?,?,?,?,?,?)";

		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			public int getBatchSize() {
				return list.size();
				// 这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
			}

			@SuppressWarnings("static-access")
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setString(1, list.get(i).getGameAccount());
				ps.setDouble(2, list.get(i).getReflectAmount());
				ps.setTimestamp(3, Timestamp.valueOf(list.get(i).getReflectTime().now()));
				ps.setInt(4, list.get(i).getReflectChannel());
				ps.setString(5, list.get(i).getBankAccount());
				ps.setInt(6, list.get(i).getStatus());

				ps.setString(7, list.get(i).getReviewer());
				ps.setTimestamp(8, Timestamp.valueOf(list.get(i).getReviewTime().now()));
				ps.setDouble(9, list.get(i).getSxfCharge());
				ps.setDouble(10, list.get(i).getDzjeAmount());
				ps.setString(11, list.get(i).getReflectChannelName());
				

			}
		});

	}

	// 用户提款
	public SumAccountInfo selectuserreflectData(LocalDateTime startTime, LocalDateTime endTime) {

		String sql = "select count(*) from member_reflect where 1=1 ";

		String sqlitem = "";
		if (startTime == null && endTime == null) {
			sqlitem += " and TO_DAYS(reflect_time) = TO_DAYS(NOW())";
		} else {
			if (startTime != null)
				sqlitem += " and reflect_time >='" + startTime + "'";

			if (endTime != null)
				sqlitem += " and reflect_time <='" + endTime + "'";
		}
		int count = jdbcTemplate.queryForObject(sql + sqlitem, Integer.class);

		String sql2 = sqlitem + " group by game_account";
		List<Integer> list = jdbcTemplate.query(sql + sql2, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				return rs.getInt(1);
			}
		});

		String sqlamount = "select sum(reflect_amount) from member_reflect where 1=1 ";
		Double totalAmount = jdbcTemplate.queryForObject(sqlamount + sqlitem, Double.class);

		SumAccountInfo info = new SumAccountInfo("", count,
				(list == null || list.size() <= 0) == true ? 0 : list.size(), totalAmount == null ? 0 : totalAmount);
		return info;
	}

}
